﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;

using OpenPop.Pop3;
using OpenPop.Mime;
using System.Collections;
using System.Net.Mail;
using System.Net;
using System.Configuration;
using System.IO;
using System.Web;
using System.Text.RegularExpressions;


    public static class DB
    {

        public static SqlConnection sqlConnection1;
        public static SqlCommand sqlCommand1;
        public static SqlDataAdapter da;
        public static SmtpClient client;
        public static OpenPop.Pop3.Pop3Client pop3Client;
        
        static DB()
        {
            try
            {
              
               sqlConnection1 = new SqlConnection( ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
              
                sqlCommand1 = new SqlCommand();
                sqlCommand1.Connection = sqlConnection1;
                client = new SmtpClient();
                client.DeliveryMethod = SmtpDeliveryMethod.Network;

                //client.EnableSsl = true;

                client.Host = "mail.dawriyah.com";

                client.Port = 587;



                // setup Smtp authentication

                System.Net.NetworkCredential credentials =
                    new System.Net.NetworkCredential("info@dawriyah.com", "dawryasystem");//smaazoon@gmail.com

                client.UseDefaultCredentials = false;

                client.Credentials = credentials;
                

                  
            }
            catch (Exception ex) { throw ex; }
        }

        public static string remove_spaces(string loadSearch)
        {
            if (loadSearch.Contains(" "))
            {
                string[] loadSearchs = loadSearch.Split(' ');

                loadSearch = "";
                int j = 0;
                for (int i = 0; i < loadSearchs.Length; i++)
                {


                    
                    if (loadSearchs[i].Trim().Length != 0)
                    {
                        j++;
                        loadSearch += loadSearchs[i] + ' '.ToString(); ;
                    }


                }
                loadSearch = loadSearch.Trim();



            }
            return loadSearch;
        }
        /// <summary>
        /// /////
        /// </summary>
        /// <returns></returns>
        public static DataRow getcurrentssettings()
        {
            DataTable ds = new DataTable();

            try
            {
                sqlCommand1.CommandText = "select * from mysettings";


                da = new SqlDataAdapter(sqlCommand1);
                da.Fill(ds);


            }
            catch (Exception ec)
            {

                //  return ds;
                throw ec;
            }


            return ds.Rows[0];

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="amount"></param>
        /// <param name="fromCurrency"></param>
        /// <param name="toCurrency"></param>
        /// <returns></returns>
        public static decimal Convert(decimal amount, string fromCurrency, string toCurrency)
        {
            WebClient web = new WebClient();

            string url = string.Format("http://www.google.com/ig/calculator?hl=en&q={2}{0}%3D%3F{1}", fromCurrency.ToUpper(), toCurrency.ToUpper(), amount);

            string response = web.DownloadString(url);

            Regex regex = new Regex("rhs: \"(\\d*.\\d*)");
            Match match = regex.Match(response);

            decimal rate = System.Convert.ToDecimal(match.Groups[1].Value);

            return rate;
        }
        /// <summary>
        /// ///
        /// </summary>
        /// <param name="path"></param>
        public static void BUP(string  path)
        {
            try
            {
                sqlCommand1.CommandText = "Use Master BACKUP  DATABASE Periodical TO DISK= N'" + path + "'";
                if (sqlConnection1.State == ConnectionState.Closed)
                {
                    sqlConnection1.Open();
                }

                sqlConnection1.ChangeDatabase("master");
                 sqlCommand1.ExecuteNonQuery();
            }
            catch (Exception ec)
            {
                throw ec;
            }
            finally
            {

                sqlConnection1.Close();
            }
        }
        ///////////////mail part///////////////////////
        //////////////////////////////////////
        //////////////////////////////////////
        //////////////////////////////////////
        /// <summary>
        /// /////
        /// </summary>
        /// <returns></returns>
        public static Pop3Client connectmail()
        {

            if (!pop3Client.Connected)
            {
                pop3Client.Connect("pop.gmail.com", 995, true);
                pop3Client.Authenticate("info.dawriyah@gmail.com", "dawryasystem");
            }
            //else
            //{
            //    pop3Client.Reset();
            //}
            
            return pop3Client;
        }
        public static void deletemail(int msgid)
        {
            if (pop3Client.Connected)
            {
                pop3Client.DeleteMessage(msgid);
                pop3Client.Disconnect();

            }
            connectmail();

        }
        public static int sendingmail(string to, string subject, string body)
        {

            MailMessage msg = new MailMessage();

            msg.From = new MailAddress("info@dawriyah.com");

            msg.To.Add(new MailAddress(to));


            //Attachment att=new Attachment(,"الملحقات");
            msg.Subject = subject;

            msg.IsBodyHtml = true;

            msg.Body = string.Format("<html><head></head><body><b>" + body + "</b></body>");




            try
            {
                // clint.Send(mms);

                client.Send(msg);
                return 1;


            }

            catch (Exception ex)
            {

                throw ex;

            }
        }
        ////////////////////////////////////////////////////
        public static int sendingmail(string to, string subject, string body, System.IO.Stream stream, string name)
        {
            MailMessage msg = new MailMessage();

            msg.From = new MailAddress("info@dawriyah.com");

            msg.To.Add(new MailAddress(to));


            Attachment att = new Attachment(stream, name);
            msg.Subject = subject;

            msg.IsBodyHtml = true;

            msg.Body = string.Format("<html><head></head><body><b>" + body + "</b></body>");
            msg.Attachments.Add(att);


            try
            {

                client.Send(msg);
                return 1;


            }

            catch (Exception ex)
            {

                return 0;

            }
        }
        public static int sendingmail(string to, string subject, string body, string[] name)
        {
            MailMessage msg = new MailMessage();

            msg.From = new MailAddress("info@dawriyah.com");

            msg.To.Add(new MailAddress(to));

            foreach (string nm in name)
            {
                // System.IO.FileStream=new FileStream(
                string[] nms = nm.Split(',');
                Attachment att = new Attachment(new MemoryStream(File.ReadAllBytes(nms[0])), nms[1]);
                msg.Attachments.Add(att);
            }
            msg.Subject = subject;

            msg.IsBodyHtml = true;

            msg.Body = string.Format("<html><head></head><body><b>" + body + "</b></body>");



            try
            {

                client.Send(msg);
                return 1;


            }

            catch (Exception ex)
            {

                return 0;

            }
        }

        public static CheckingHistory getCheckHistory(string filter,string id)
        {

            CheckingHistory ds = new CheckingHistory();

            try
            {
                sqlCommand1.CommandText = "select d.checked,d.datechecked,d.delivered,d.issuedate,d.issueno,d.receivedcopy,d.receiveddate,d.user_id,t.* ,u.username,d.id as mysub,t.product_id as pro  from users u Right outer JOIN  daily_checking d  ON (d.user_id = u.id)  INNER JOIN company_product t ON (d.company_product_id = t.id)" + filter;


                da = new SqlDataAdapter(sqlCommand1);
                da.Fill(ds.here);
                SqlCommand command = new SqlCommand("getallProductinfo", sqlConnection1);

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@id", id);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds.getallProductinfo);

            }
            catch (Exception ec)
            {

                //  return ds;
                throw ec;
            }


            return ds;

        }

        public static SubHistoryreps getSubHistoryrep( string id)
        {

            SubHistoryreps ds = new SubHistoryreps();

            try
            {

                SqlCommand command = new SqlCommand("SubHistoryrep", sqlConnection1);

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@id", id);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds.SubHistoryrep);

            }
            catch (Exception ec)
            {

                //  return ds;
                throw ec;
            }


            return ds;

        }

        //public static SubHistoryreps getSubHistoryrep(string id)
        //{

        //    SubHistoryreps ds = new SubHistoryreps();

        //    try
        //    {

        //        SqlCommand command = new SqlCommand("SubHistoryrep", sqlConnection1);

        //        command.CommandType = CommandType.StoredProcedure;
        //        command.Parameters.AddWithValue("@id", id);
        //        SqlDataAdapter adapter = new SqlDataAdapter(command);
        //        adapter.Fill(ds.SubHistoryrep);

        //    }
        //    catch (Exception ec)
        //    {

        //        //  return ds;
        //        throw ec;
        //    }


        //    return ds;

        //}
        public static InternalHistories getIntHistoryrep(string filter,string internalID, string company_product_id,string delivered,string userid,string fromcopyno,string tocopyno,string fromdate , string todate)
        {

            InternalHistories ds = new InternalHistories();

            try
            {
                SqlCommand command = new SqlCommand(filter, sqlConnection1);
                command.Parameters.AddWithValue("@internalID", internalID);
                command.Parameters.AddWithValue("@company_product_id", company_product_id);
                command.Parameters.AddWithValue("@delivered", delivered);
                command.Parameters.AddWithValue("@userid", userid);
                command.Parameters.AddWithValue("@fromcopyno", fromcopyno);
                command.Parameters.AddWithValue("@tocopyno", tocopyno);
                command.Parameters.AddWithValue("@fromdate", fromdate);
                command.Parameters.AddWithValue("@todate", todate);

                foreach (SqlParameter  param in command.Parameters)
                {
                    param.IsNullable = true;
                    if (param.Value == null)
                        param.Value = DBNull.Value;
                }


                
                
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds.checking);
                command.CommandText = "select p.product,i.name as [internal] ,c.company from internal_distribution i INNER JOIN  companies c ON (i.company_id = c.id) INNER JOIN  company_product co ON (co.company_id = c.id)  INNER JOIN  products p ON (co.product_id = p.id) where co.id= @company_product_id  and i.id=@internalID";
                 adapter = new SqlDataAdapter(command);
                adapter.Fill(ds.internalinfo);
            }
            catch (Exception ec)
            {

                //  return ds;
                throw ec;
            }


            return ds;

        }
        //////////////////////////////////////
        //////////////////////////////////////
        //////////////////////////////////////
        //////////////////////////////////////
        public static CustomerReceipt getIntReceipt(string filter, string intid)
        {

            CustomerReceipt ds = new CustomerReceipt();

            try
            {
                SqlCommand command = new SqlCommand("select i.*,t.copyprice from internal_checking i INNER JOIN company_product t ON (i.company_product_id = t.id) where i.internal_id=@int_id" + filter, sqlConnection1);

                //command.Parameters.AddWithValue("@id", proid);
                command.Parameters.AddWithValue("@int_id", intid);

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds.copies);
                command.CommandText = "";
                
                if (!filter.Contains(" in"))
                {
                    command.CommandText = "select p.product,t.id,publisher from products p INNER JOIN company_product t ON (t.product_id = p.id) where t.id in( select company_product_id from  internal_checking where internal_id=@int_id )";//i.name as [internal] ,c.company from internal_distribution i INNER JOIN  companies c ON (i.company_id = c.id) INNER JOIN  company_product co ON (co.company_id = c.id)  INNER JOIN  products p ON (co.product_id = p.id) where co.id= @id  and i.id=@intid";
                }
                else
                {
                 string [] filtrs=filter.Split(new []{"  and"}, StringSplitOptions.None);
                 command.CommandText = "select p.product,t.id ,p.publisher from products p INNER JOIN company_product t ON (t.product_id = p.id) where " + filtrs[1];//i.name as [internal] ,c.company from internal_distribution i INNER JOIN  companies c ON (i.company_id = c.id) INNER JOIN  company_product co ON (co.company_id = c.id)  INNER JOIN  products p ON (co.product_id = p.id) where co.id= @id  and i.id=@intid";
                
                }
               // command.Parameters.AddWithValue("@int_id", intid);
                
                
                adapter = new SqlDataAdapter(command);
                adapter.Fill(ds.products);
                command.CommandText = "select i.name,i.internal,c.company from internal_distribution i inner join companies c on (c.id=i.company_id) where i.id=@int_id";
                adapter = new SqlDataAdapter(command);
                adapter.Fill(ds.customers);
            }
            catch (Exception ec)
            {

                //  return ds;
                throw ec;
            }


            return ds;

        }
        //////////////////////////////////////
        //////////////////////////////////////


        /// <summary>
        /// //
        /// </summary>
        /// <param name="ip"></param>
        /// <returns></returns>

        public static DataTable getMarc(int id)
        {
            DataTable ds = new DataTable();
           
            try
            {
                sqlCommand1.CommandText = "select * from tags where product_id= " + id;


                da = new SqlDataAdapter(sqlCommand1);
                da.Fill(ds);
                
            }
            catch (Exception ec)
            {

                //  return ds;
                throw ec;
            }


            return ds;

        }
        public static DataSet getMarc(string id)
        {
            DataSet ds = new DataSet();

            try
            {
                SqlCommand command = new SqlCommand("getmarc", sqlConnection1);

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@sid", id);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds);

            }
            catch (Exception ec)
            {

                //  return ds;
                throw ec;
            }


            return ds;

        }
        /////////////////
        public static DataSet getproc(string procedure,string id)
        {
            DataSet ds = new DataSet();

            try
            {
                string[] ids=id.Split(',');
                SqlCommand command = new SqlCommand(procedure, sqlConnection1);

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue(ids[0], ids[1]);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds);

            }
            catch (Exception ec)
            {

                //  return ds;
                throw ec;
            }


            return ds;

        }
        /// <summary>
        /// ////////////////////////////////
        /// </summary>
        /// <param name="procedure"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static DataSet getproc(string procedure, List<string> parmas)
        {
            DataSet ds = new DataSet();

            try
            {
               
                SqlCommand command = new SqlCommand(procedure, sqlConnection1);

                command.CommandType = CommandType.StoredProcedure;
                foreach (string id in parmas)
                {
                    string[] ids = id.Split(',');
                    command.Parameters.AddWithValue(ids[0], ids[1]);
                }
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds);

            }
            catch (Exception ec)
            {

                //  return ds;
                throw ec;
            }


            return ds;

        }
        ///////////////////////////////////////
        public static DataSet getdata(string command)
        {
            DataSet ds = new DataSet();

            try
            {
                sqlCommand1.CommandText = command;


                da = new SqlDataAdapter(sqlCommand1);
                da.Fill(ds);

            }
            catch (Exception ec)
            {

                //  return ds;
                throw ec;
            }


            return ds;

        }
        ////////////////////

        public static DataTable Authenticate(string user, string pass)
        {
            DataTable ds = new DataTable();

            try
            {
                sqlCommand1.CommandText = "select u.*,c.active from companies c right JOIN users u ON (u.company_id= c.id) where u.username ='" + user + "' and u.password='" + pass + "'";


                da = new SqlDataAdapter(sqlCommand1);
                da.Fill(ds);

            }
            catch (Exception ec)
            {

                //  return ds;
                throw ec;
            }


            return ds;

        }
        ////////////////////



        /// <summary>
        /// //
        /// </summary>
        /// <param name="ip"></param>
        /// <returns></returns>
         public static DataSet checkip(int ip)
         {
             DataSet ds = new DataSet();
             ds.Tables.Add("ipinfo");
             ds.Tables.Add("allvisitors");
             try
             {
                 sqlCommand1.CommandText = "select * from CountryIps where " + ip + " between  IPFROM  and IPTO  ";


                 da = new SqlDataAdapter(sqlCommand1);
                 da.Fill(ds.Tables["ipinfo"]);
                 sqlCommand1.CommandText = "select SUM(Visitors) from  CountryIps ";
                 da.SelectCommand = sqlCommand1;
                 da.Fill(ds.Tables["allvisitors"]);
             }
             catch (Exception ec)
             {

                 //  return ds;
                 throw ec;
             }


             return ds;
            
         }

         public static CompaniesSet datasetfill(string id,string sid)
         {
             CompaniesSet ds = new CompaniesSet();

             try
             {
                 SqlCommand command = new SqlCommand("getallcustomerinfo", sqlConnection1);

                 command.CommandType = CommandType.StoredProcedure;
                 command.Parameters.AddWithValue("@id", id);
                 SqlDataAdapter adapter = new SqlDataAdapter(command);
                 adapter.Fill(ds, "getallcustomerinfo");
                 SqlCommand command2 = new SqlCommand("getcurrentsub", sqlConnection1);
                 command2.Parameters.AddWithValue("@id", sid);
                 command2.CommandType = CommandType.StoredProcedure;
                 SqlDataAdapter adapter2 = new SqlDataAdapter(command2);
                 adapter2.SelectCommand = command2;
                 adapter2.Fill(ds, "getcurrentsub");
                




             }
             catch (Exception ec)
             {

                 //  return ds;
                 throw ec;
             }
             return ds;

         }
         //////////////

         public static CompnySubs SubsRepfill(string id, string sid,string mid)
         {
             CompnySubs ds = new CompnySubs();

             try
             {
                 SqlCommand command = new SqlCommand("getCompSubInfo", sqlConnection1);
                 command.CommandType = CommandType.StoredProcedure;
                 command.Parameters.AddWithValue("@id", sid);
                 command.Parameters.AddWithValue("@coid",id);
                 SqlDataAdapter adapter = new SqlDataAdapter(command);
                 adapter.Fill(ds, "getCompSubInfo");
                 SqlCommand command2 = new SqlCommand("getstats2", sqlConnection1);
                 command2.Parameters.AddWithValue("@id", mid);
                 command2.CommandType = CommandType.StoredProcedure;
                 SqlDataAdapter adapter2 = new SqlDataAdapter(command2);
                 adapter2.SelectCommand = command2;
                 adapter2.Fill(ds, "getstats2");





             }
             catch (Exception ec)
             {

                 //  return ds;
                 throw ec;
             }
             return ds;

         }

         public static Customerallinfo Customerallinfos(string id, string sid)
         {
             Customerallinfo ds = new Customerallinfo();
         //http://localhost:6814/Periodicals/reps2.aspx?rep=customerallsubs&id=3&sid=0&did=3
             try
             {
                 SqlCommand command = new SqlCommand("getcusinfocurrentsub", sqlConnection1);
                 command.CommandType = CommandType.StoredProcedure;
                 command.Parameters.AddWithValue("@id", id);

                 SqlDataAdapter adapter = new SqlDataAdapter(command);
                 adapter.Fill(ds, "getcusinfocurrentsub");
                 ///////////////////////////////////////////////////////
                 command.CommandText = "getCompSubInfo";
                 command.Parameters.Clear();
                 command.CommandType = CommandType.StoredProcedure;
                 command.Parameters.AddWithValue("@id", sid);
                 command.Parameters.AddWithValue("@coid",id);
                 adapter.SelectCommand = command;
                 adapter.Fill(ds, "getCompSubInfo");
                 //////////////////////////////////////////////
                 SqlCommand command2 = new SqlCommand("getstats2", sqlConnection1);
                 command2.Parameters.AddWithValue("@id",id);
                 command2.CommandType = CommandType.StoredProcedure;
                 SqlDataAdapter adapter2 = new SqlDataAdapter(command2);
                 adapter2.SelectCommand = command2;
                 adapter2.Fill(ds, "getstats2");





             }
             catch (Exception ec)
             {

                 //  return ds;
                 throw ec;
             }
             return ds;

         }
        
       ///////////////////////////////////
         public static DataSet Customerallinfo2(string id, string sid)
         {
             DataSet ds = new DataSet();
             //http://localhost:6814/Periodicals/reps2.aspx?rep=customerallsubs&id=3&sid=0&did=3
             try
             {
                

                 
               
                 ///////////////////////////////////////////////////////
                 SqlCommand command = new SqlCommand("getCompSubInfo", sqlConnection1);
              
                 command.CommandType = CommandType.StoredProcedure;
                 command.Parameters.AddWithValue("@id", sid);
                 command.Parameters.AddWithValue("@coid", id);
                 SqlDataAdapter adapter = new SqlDataAdapter(command);
                 adapter.Fill(ds, "getCompSubInfo");
                 //////////////////////////////////////////////
                 SqlCommand command2 = new SqlCommand("getstats2", sqlConnection1);
                 command2.Parameters.AddWithValue("@id", id);
                 command2.CommandType = CommandType.StoredProcedure;
                 SqlDataAdapter adapter2 = new SqlDataAdapter(command2);
                 adapter2.SelectCommand = command2;
                 adapter2.Fill(ds, "getstats2");





             }
             catch (Exception ec)
             {

                 //  return ds;
                 throw ec;
             }
             return ds;

         }
         ////////
        /// <summary>
        /// 
        /// </summary>
        /// <param name="table"></param>
        /// <param name="filed_serach"></param>
        /// <returns></returns>
         public static System.Data.DataSet quick_search(string table, string filed_serach)
         {
             DataSet ds = new DataSet();
             List<string> tables_names = new List<string>();


             try
             {
                 sqlCommand1.CommandText = "select column_name from information_schema.columns where table_name = '" + table + "'";
                 sqlConnection1.Open();
                 SqlDataReader sdr = sqlCommand1.ExecuteReader();
                 sqlCommand1.CommandText = "select *  from " + table + " where ";

                 while (sdr.Read())
                 {
                     sqlCommand1.CommandText += " " + sdr[0] + " like N'%" + filed_serach + "%' or";

                 }
                 sqlConnection1.Close();
                 sqlCommand1.CommandText = sqlCommand1.CommandText.TrimEnd('r', 'o');


                 da = new SqlDataAdapter(sqlCommand1);
                 da.Fill(ds);




                 return ds;
             }
             catch (Exception ec)
             {

                 //  return ds;
                 throw ec;
             }
             finally
             {

                 sqlConnection1.Close();
             }


         }
         public static System.Data.DataSet quick_search(string table, string filed_serach, string coloumn)
         {
             DataSet ds = new DataSet();



             try
             {

                 sqlCommand1.CommandText = "select companyname,id,address,importance,city,tele1  from " + table + " where " + coloumn + " like N'%" + filed_serach + "%'";

                 da = new SqlDataAdapter(sqlCommand1);
                 da.Fill(ds);




                 return ds;
             }
             catch (Exception ec)
             {

                 //  return ds;
                 throw ec;
             }


         }
         public static int INSERTag(string tag,string ind,int proid,string subfield,string field)
         {
           if (sqlConnection1.State == ConnectionState.Closed)
            {
                sqlConnection1.Open();
            }
           sqlCommand1.CommandText = " insert into tags( tag,indicator,product_id,subfield,field)values('" + tag + "','" + ind + "'," + proid + ",'"+subfield +"','"+field+"')";//SET @MID = SCOPE_IDENTITY()

         int  ins = sqlCommand1.ExecuteNonQuery();

           return ins;
         }


         public static void adddate(string product, string dates, string company, string del)
         {
             if (sqlConnection1.State == ConnectionState.Closed)
             {
                 sqlConnection1.Open();
             }
             sqlCommand1.CommandText = " insert into daily_checking( company_product_id,issuedate,company_id,delivered)values(" + product + ",'" + dates + "'," + company + "," + del + ")";//SET @MID = SCOPE_IDENTITY()

              int ins = sqlCommand1.ExecuteNonQuery();
         }
         public static void MonthlyJob()
         {
             DataTable dt = new DataTable();
            
        
               DateTime myfirst=new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
                string  first = myfirst.AddMonths(1).ToString("yyyy-MM-dd");

                DateTime date = myfirst.AddMonths(2).AddDays(-1);
                 
                string last =  date.ToString("yyyy-MM-dd");
                sqlCommand1.CommandText = "select c.*,f.*,c.id as mysub from company_product c INNER JOIN frequencies f   ON (f.id = c.freq_id) where company_id in(select id from companies where active=1) and   enddate >'" + first + "'";
               da=new SqlDataAdapter(sqlCommand1);
              da.Fill(dt);
              if (dt.Rows.Count > 0)
               {
                  for (int i = 0; i < dt.Rows.Count; i++)
                  {

                      DateTime date2 = DateTime.Now;
                      DateTime subdate =DateTime.Parse(dt.Rows[i]["enddate"].ToString());
                      DataTable df = new DataTable();
                      string product = dt.Rows[i]["mysub"].ToString();
                      string kk = dt.Rows[i]["type"].ToString();
                      string company_id=dt.Rows[i]["company_id"].ToString();
                      sqlCommand1.CommandText = "select top 1 issuedate from daily_checking where  company_product_id=" + dt.Rows[i]["mysub"].ToString() + " ORDER BY issuedate DESC";
                      da.SelectCommand = sqlCommand1;
                       da.Fill(df);
                       if (df.Rows.Count > 0)
                       {
                           date2 = DateTime.Parse(df.Rows[0]["issuedate"].ToString());

                        
                       }
                       else
                       {
                           date2 = DateTime.Parse(dt.Rows[i]["startdate"].ToString());
                      
                       }
                     int  vv = int.Parse(dt.Rows[i]["nodays"].ToString());
                      switch (kk)
                      {
                          case "w":
                              date2 = date2.AddDays(vv * 7);
                              if ((date2 <= date) && (date2 <= subdate))
                              {
                                  adddate(product, date2.ToShortDateString(), company_id, "0");

                                  goto case "w";
                              }
                              else
                              {
                                  break;
                              }
                          case "d":
                              date2 = date2.AddDays(vv * 1);
                             
                              if ((date2 <= date) && (date2 <= subdate))
                              {
                                  adddate(product, date2.ToShortDateString(), company_id, "0");
                                  goto case "d";
                              }
                              else
                              {
                                 
                                  break;
                              }
                          case "m":
                            
                              date2 = date2.AddMonths(vv);
                              if ((date2 <= date) && (date2 <= subdate))
                              {
                                  adddate(product, date2.ToShortDateString(), company_id, "0");
                                  goto case "m";

                              }

                              break;

                          default:

                              break;

                      }

                  }
              
              }
           

           

             
         }
         //
         /// <summary>
         /// 
         /// </summary>
         /// <returns></returns>
         public static int INSERT2(string tbl, List<string> fields, List<string> types, ArrayList values)
         {
             int ins;
             sqlCommand1.Parameters.Clear();
             //  SqlParameter IDParameter = new SqlParameter("@MID", SqlDbType.Int);
             try
             {

                 string m_fields = "";
                 string m_values = "";
                 foreach (var x in fields)
                 {
                     m_fields += x + ",";
                 }
                 for (int i = 0; i < values.Count; i++)
                 {
                     if (types[i] == "string")
                     {
                         m_values += "N'" + values[i] + "',";

                     }
                     else
                     {
                         if (types[i] == "datetime")
                         {
                             string param = "@" + fields[i];
                             m_values += param + ",";
                             sqlCommand1.Parameters.Add(param, SqlDbType.DateTime).Value = values[i];
                         }
                         else
                         {
                             m_values += values[i] + ",";
                         }
                     }

                 }
                 m_fields = m_fields.Trim(",".ToCharArray()[0]);
                 m_values = m_values.Trim(",".ToCharArray()[0]);
                 if (sqlConnection1.State == ConnectionState.Closed)
                 {
                     sqlConnection1.Open();
                 }
                 sqlCommand1.CommandText = "SET NOCOUNT ON insert into " + tbl + "( " + m_fields + ")values(" + m_values + "); select scope_identity()";//SET @MID = SCOPE_IDENTITY()

                 decimal inss = (decimal)sqlCommand1.ExecuteScalar();

                 ins = decimal.ToInt32(inss);



             }
             catch (Exception f)
             {

                 throw f;

             }
             finally
             {
                 sqlCommand1.Parameters.Clear();
                 sqlConnection1.Close();


             }
             return ins;
         }
         ///////////////////////////////

   
/// <summary>
/// ////
/// </summary>
/// <param name="tbl"></param>
/// <param name="fields"></param>
/// <param name="types"></param>
/// <param name="values"></param>
/// <returns></returns>
    public static int INSERT(string tbl, List<string> fields, List<string> types, ArrayList values)
    {
        int ins;
        sqlCommand1.Parameters.Clear();
        try
        {

            string m_fields = "";
            string m_values = "";
            foreach (var x in fields)
            {
                m_fields += x + ",";
            }
            for (int i = 0; i < values.Count; i++)
            {
                if (types[i] == "string")
                {
                    m_values += "N'" + values[i] + "',";

                }
                else
                {
                    if (types[i] == "datetime")
                    {
                        string param = "@" + fields[i];
                        m_values += param + ",";
                        sqlCommand1.Parameters.Add(param, SqlDbType.DateTime).Value = values[i];
                    }
                    else
                    {
                        m_values += values[i] + ",";
                    }
                }

            }
            m_fields = m_fields.Trim(",".ToCharArray()[0]);
            m_values = m_values.Trim(",".ToCharArray()[0]);
            if (sqlConnection1.State == ConnectionState.Closed)
            {
                sqlConnection1.Open();
            }
            sqlCommand1.CommandText = " insert into " + tbl + "( " + m_fields + ")values(" + m_values + ")";//SET @MID = SCOPE_IDENTITY()

           ins = sqlCommand1.ExecuteNonQuery();

           return ins;
           

        }
        catch (Exception f)
        {
            throw f;
            //return -1;

        }
        finally
        {
            sqlCommand1.Parameters.Clear();
            sqlConnection1.Close();
           


        }
        
    }

    //////////// delete////////////////////////
    public static int DELETE(string tbl, string field, string type, object value)
    {
        int ins;
        try
        {
            
            if (sqlConnection1.State == ConnectionState.Closed)
            {
                sqlConnection1.Open();
            }
            if (type == "string")
            {
                value = "'" + value + "'";

            }

            sqlCommand1.CommandText = "delete FROM  " + tbl + " where  " + field + "=" + value;
            ins = sqlCommand1.ExecuteNonQuery();
            

        }
        catch (Exception f)
        {

            ins = -1;
           // throw f;

        }
        finally
        {
            sqlConnection1.Close();



        }
        return ins;

    }
        ///////////////////////
        ///////////////
    //////////// UPDATE////////////////////////
    public static int UPDATE(string tbl, string pk, List<string> fields, List<string> types, ArrayList values)
      
    {
          int ins;
        string[] pkfields; 
        try
        {
            string m_fields = "update  " + tbl + " set ";
            pkfields = pk.Split(',');
            if(pkfields[0]=="string")
            {
               pkfields[2]="'"+pkfields[2]+"'";
            }

            for (int i = 0; i < fields.Count; i++)
            {
                if (types[i] == "string")
                {
                    m_fields += fields[i] + "=N'" + values[i] +"'," ;

                }
                else
                {

                    if (types[i] == "datetime")
                    {

                        m_fields += fields[i] + "= CAST('" + values[i] + "' AS datetime),";
                    }
                    else
                    {

                        m_fields += fields[i] + "=" + values[i] + ",";
                    }
                    
                }

            }
            m_fields = m_fields.Trim(",".ToCharArray()[0]);

            m_fields += "where " + pkfields[1] + "=" + pkfields[2];
            if (sqlConnection1.State == ConnectionState.Closed)
            {
                sqlConnection1.Open();
            }
            sqlCommand1.CommandText = m_fields;// + m_fields + "=" + m_values + ")";

          ins = sqlCommand1.ExecuteNonQuery();
         
           
        }
        catch (Exception f)
        {
            throw f;

        }
        finally
        {
            sqlConnection1.Close();
           
        }

        return ins;

    }


///////////////////////////////////// download////////////
        /// <summary>
        /// 
        /// </summary>
        /// <param name="Response" ></param>
        /// <param name="_fullPath"></param>
        /// <param name="filePath"></param>
        /// <param name="_FileName"></param>
        /// <returns></returns>
    public static bool ResponseFile(HttpResponse Response, string _fullPath, string filePath, string _FileName)
    {
        System.IO.FileInfo FileName = new System.IO.FileInfo(_fullPath);
        FileStream myFile = new FileStream(_fullPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
        BinaryReader _BinaryReader = new BinaryReader(myFile);
        if (FileName.Exists)
        {
            try
            {
                long startBytes = 0;
                string lastUpdateTiemStamp = File.GetLastWriteTimeUtc(filePath).ToString("r");
                string _EncodedData = HttpUtility.UrlEncode(_FileName, Encoding.UTF8) + lastUpdateTiemStamp;

                Response.Clear();
                Response.Buffer = false;
                Response.AddHeader("Accept-Ranges", "bytes");
                Response.AppendHeader("ETag", "\"" + _EncodedData + "\"");
                Response.AppendHeader("Last-Modified", lastUpdateTiemStamp);
                Response.ContentType = "application/octet-stream";
                Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName.Name);
                Response.AddHeader("Content-Length", (FileName.Length - startBytes).ToString());
                Response.AddHeader("Connection", "Keep-Alive");
                Response.ContentEncoding = Encoding.UTF8;

                //Send data
                _BinaryReader.BaseStream.Seek(startBytes, SeekOrigin.Begin);

                //Dividing the data in 1024 bytes package
                int maxCount = (int)Math.Ceiling((FileName.Length - startBytes + 0.0) / 1024);

                //Download in block of 1024 bytes
                int i;
                for (i = 0; i < maxCount && Response.IsClientConnected; i++)
                {
                    Response.BinaryWrite(_BinaryReader.ReadBytes(1024));
                    Response.Flush();
                }
                //if blocks transfered not equals total number of blocks
                if (i < maxCount)
                    return false;
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                Response.End();
                _BinaryReader.Close();
                myFile.Close();
            }
        }
        return true;
    }
        /////////////////////
    //////////// UPDATE overloaded ////////////////////////
    public static int UPDATE(string tbl, string pk, string fields, string types, string values)
    {
        int ins;
        string[] pkfields;
        try
        {
            pkfields = pk.Split(',');
            if (pkfields[0] == "string")
            {
                pkfields[2] = "'" + pkfields[2] + "'";
            }


            if (types == "string")
            {
                values = "'" + values + "'";

            }
            else
            {
                if (types == "datetime")
                {
                  //  string param = "@" + fields;
                   // values = param ;
                    //sqlCommand1.Parameters.Add(param, SqlDbType.DateTime).Value = 
                    values="CAST('" + values + "' AS datetime)";
                }
            
            }


               string m_fields = "update  " + tbl + " set " + fields + " =" + values;
           
            //m_fields = m_fields.Trim(",".ToCharArray()[0]);

            m_fields += " where " + pkfields[1] + "=" + pkfields[2];
            if (sqlConnection1.State == ConnectionState.Closed)
            {
                sqlConnection1.Open();
            }
            sqlCommand1.CommandText = m_fields;// + m_fields + "=" + m_values + ")";

            ins = sqlCommand1.ExecuteNonQuery();
           

        }
        catch (Exception f)
        {
            throw f;

        }
        finally
        {
           // sqlCommand1.Parameters.Clear();
            sqlConnection1.Close();

        }

        return ins;

    }

        /////////////// delete overrloaded
    public static int DELETE(string tbl, string pk)
    {
        int ins;
        string[] pkfields;
        try
        {
            pkfields = pk.Split(',');
            if (pkfields[0] == "string")
            {
                pkfields[2] = "'" + pkfields[2] + "'";
            }


            string m_fields = "delete from " + tbl + " where " + pkfields[1] + "=" + pkfields[2];

            if (sqlConnection1.State == ConnectionState.Closed)
            {
                sqlConnection1.Open();
            }
            sqlCommand1.CommandText = m_fields;// + m_fields + "=" + m_values + ")";

            ins = sqlCommand1.ExecuteNonQuery();


        }
        catch (Exception f)
        {
            return -1;
            //throw f;

        }
        finally
        {
            sqlConnection1.Close();

        }

        return ins;

    }


    public static DateTime GetSubDate(string compID)
    {
        DataSet dss = DB.getdata("select endofsub from [subscriptions]   WHERE endofsub>=getdate() and [company_id] = " + compID);
        if (dss.Tables[0].Rows.Count > 0)
        {
            return System.Convert.ToDateTime(dss.Tables[0].Rows[0][0]);
        }
        else return DateTime.MinValue;
                       
    }
        public static void setpendingobjectscash()
        {
  
            System.Data.DataSet ds  =  DB.getdata("Select [type], Count(*) as [count]   from pendingobjects group by [type] ");
           System.Data.DataTable dt = ds.Tables[0];
             HttpContext.Current.Cache["pendingobjects"] = dt;
           
       
        }
        public static string getnewsbar()
        {
            
            if (HttpContext.Current.Cache["newsbar"] == null)
            {
                DataSet ds = DB.getdata("select banner from adminsettings");
                string bannerdata;
                if (ds.Tables[0].Rows.Count > 0)
                {
                    bannerdata = ds.Tables[0].Rows[0][0].ToString();
                    bannerdata = bannerdata.Replace("<br />", "****");
                    bannerdata = bannerdata.Replace("<br/>", "****");
                    bannerdata = bannerdata.TrimEnd('*');
                    HttpContext.Current.Cache["newsbar"] = bannerdata;
                    return bannerdata;
                }
                else
                { bannerdata = "news will be added soon"; HttpContext.Current.Cache["newsbar"] = bannerdata; return bannerdata; }
            }
            else
            {
                return HttpContext.Current.Cache["newsbar"].ToString();
            }
        }
    
}